IF OBJECT_ID('dbo.RptPCROAnalystPendingReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROAnalystPendingReport
    IF OBJECT_ID('dbo.RptPCROAnalystPendingReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROAnalystPendingReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROAnalystPendingReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPCROAnalystPendingReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 08/20/2008
--
-- Description: Source of Report, "PCROAnalystPendingReport"
--                  In Insight DB Reporting Services
--
-- Returns a list of Currently Pending Deliverables/Agreements by 
-- PreAwardManager
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptPCROAnalystPendingReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 08/20/08    KC        Created
-- 09/15/08    KC        Added Institution IS NULL
--
-- =======================================================================
CREATE PROCEDURE dbo.RptPCROAnalystPendingReport
     (
     @PreAwardManager                   VARCHAR(100),  
     @IncludeDanaFarber                 VARCHAR(3) = NULL,
     @IncludeUnassigned                 INT=0,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error         INT,
               @RowCount      INT,
               @Institution   VARCHAR(50)

     -- ALL checking
     IF @PreAWardManager IN ('ALL','','0') SET @PreAWardManager = NULL
     IF @IncludeUnassigned != 1 SET @IncludeUnassigned = 0
     
     -- Check DanaFarber
     IF UPPER(@IncludeDanaFarber) != 'YES'
          SET @IncludeDanaFarber = NULL

     -- Set a list of Institutions
     IF @IncludeDanaFarber IS NOT NULL
          SET  @Institution = 'BWH,MGH,NWH,SRH,DFCI,DFPCC'
     ELSE
          SET  @Institution = 'BWH,MGH,NWH,SRH'

     -- Return Result Set
     SELECT    ISNULL(AI.PreAwardManagerId,0) AS PreAwardManagerId,
               ISNULL(AI.PreAwardManager,'UA') AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Unassigned') AS PreAwardManagerName,
               AI.AgreementId AS AgreementId,
               AI.FolderNumber AS FolderNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               LEFT(AI.PrincipalInvestigator,CHARINDEX(',',AI.PrincipalInvestigator)-1) AS PILastName,
               AI.SponsorId AS SponsorId,
               AI.SponsorName AS SponsorName,
               AI.ProposalShortTitle AS ProposalShortTitle,                
               AI.SponsorName AS PrimarySponsor,
               AI.SubActivityType AS SubActivityType,
               AI.SecondarySponsor AS SecondarySponsor,
               AI.CSCRCaseManager AS CSCRCaseManager,
               AI.CSCRCaseManagerName AS CSCRCaseManagerName,
               AI.AgreementType AS AgreementType,
               AI.InstrumentType AS ProposalType,
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.ProposalStatus AS ProposalStatus,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.ResourceId AS AgrResourceId,
               DELS.REPORTCAT AS DeliverableType,
               DELS.MaxPendingDate AS MaxPendingDate
     FROM      dbo.AgrAgreement_Info AI
     
               -- Current Pending Deliverables
               INNER JOIN (SELECT  DISTINCT D.prop_no,
                                 D.ReportId, 
                                 D.REPORTCAT_CODE, 
                                 D.REPORTCAT,
                                 dbo.fnRemoveTime(C.MaxProcessedDate) AS MaxPendingDate
                         FROM    dbo.vwInfoEdCSCRDeliverablesAll D
                                 INNER JOIN (
                                             -- Latest statusdate
                                             SELECT  D.ReportId, MAX(D.ProcessedDate) AS MaxProcessedDate
                                             FROM    dbo.vwInfoEdCSCRDeliverablesAll D
                                             GROUP BY D.ReportId ) C ON D.ReportId = C.ReportId 
                                                 AND D.ProcessedDate = C.MaxProcessedDate
                         WHERE   D.REPORTSTAT_CODE = 'REPORTSTAT_61' AND -- Pending
                                 D.REPORTCAT_CODE IN ('REPORTCAT_35',    -- CSCR - Subcontractor Working Budget
                                                     'REPORTCAT_30',     -- CSCR - Working Budget
                                                     'REPORTCAT_42',     -- CSCR - Working Budget Amendment
                                                     'REPORTCAT_31',     -- CSCR - Device
                                                     'REPORTCAT_41')     -- CSCR - MCA
                                 -- Ignore this Mega-Conversion date! 3700+ records!    )  
                                 AND dbo.fnRemoveTime(C.MaxProcessedDate) != '8/12/2007' ) DELS ON AI.InfoEdPropNumber = DELS.prop_no
     
     WHERE     (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institution)) OR AI.Institution IS NULL) AND
               (@PreAWardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@PreAWardManager))) AND
               (@IncludeUnassigned = 1 OR AI.PreAwardManagerId IS NOT NULL) 
     ORDER BY ISNULL(AI.PreAwardManagerName,'Unknown')
     
     -- Error Checking     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptPCROAnalystPendingReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROAnalystPendingReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROAnalystPendingReport >>>'
go

GRANT EXECUTE ON dbo.RptPCROAnalystPendingReport TO InsightReport,Insight
go
